-- @owner: @zou_jialiang050
-- @date: 2022/02/24
-- @testpoint: 在更新src表前触发设置des表id3值为src表中最小id1值对应的id3

--step1:建表;expect:成功
drop table if exists t_trigger_src cascade;
drop table if exists t_trigger_des cascade;
create table t_trigger_src(id1 int, id2 int, id3 int) with (storage_type=ustore);
create table t_trigger_des(id1 int, id2 int, id3 int) with (storage_type=ustore);

--step2:在更新src表前触发设置des表id3值为src表中最小id1值对应的id3;expect:成功 des表id1=1行id3=1
create or replace function tri_update_func() returns trigger as
$$
  declare
  begin
  update t_trigger_des set id3 = (select id3 from t_trigger_src order by id1 limit 1);
  return old;
  end
$$ language plpgsql;
/
create trigger update_trigger before update on t_trigger_src
   execute procedure tri_update_func();
/
insert into t_trigger_src values(1,1,1),(2,2,2);
insert into t_trigger_des values(1,1,1),(2,2,2);
select * from t_trigger_des order by id1;
update t_trigger_src set id3=10 where id1=1;
select * from t_trigger_des order by id1;
select * from t_trigger_src order by id1;

--step3:清理环境;expect:成功
drop table if exists t_trigger_src cascade;
drop table if exists t_trigger_des cascade;